package com.nus.database;

import com.nus.beans.*;
import com.nus.database.util.DataReadUtils;
import com.nus.services.ConversionService;
import com.nus.services.EncodingService;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: dell
 * Date: 9/16/12
 * Time: 1:25 AM
 * To change this template use File | Settings | File Templates.
 */
public class UserDBManager extends DBManager {
    public UserDBManager(Connection connection) {
        super(connection);
    }

    public User getUser(String loginName) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            statement = connection.prepareStatement(SQLStatements.USER_DATA_BY_LOGIN_NAME_SQL);
            statement.setString(1, loginName.toUpperCase());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                user = DataReadUtils.getUserInformation(resultSet);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return user;
    }

    public User getUser(int userId) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        int dbUserType;
        try {
            statement = connection.prepareStatement(SQLStatements.USER_DATA_BY_USER_ID_SQL);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                user = DataReadUtils.getUserInformation(resultSet);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return user;
    }

    public void createUser(User user) {
        try {
            setAutoCommit(false);
            createApplicationUser(user);
            createDatabaseUser(user);
            commit();
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            setAutoCommit(true);
        }
    }

    public void createApplicationUser(User user) throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.CREATE_USER_SQL);
            int userTypeDB, userStatusDB, userSubTypeDB;
            ConversionService conversionService = new ConversionService();
            statement.setString(1, user.getLoginName());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getLastName());
            statement.setString(4, user.getFirstName());
            statement.setString(5, user.getAddress());
            statement.setLong(6, user.getPhoneNumber());
            statement.setDate(7, new java.sql.Date(user.getDateOfBirth().getTime()));
            statement.setLong(8, user.getBasicSalary());

            userTypeDB = conversionService.getUserType(user.getUserType());
            statement.setInt(9, userTypeDB);
            statement.setInt(10, user.getRemainingLeaves());

            userStatusDB = conversionService.getUserStatusDB(user.getStatus());
            statement.setInt(11, userStatusDB);

            userSubTypeDB = conversionService.getUserSubTypeDB(user.getUserSubType());
            statement.setInt(12, userSubTypeDB);

            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    public void createDatabaseUser(User user) throws SQLException {
        EncodingService encodingService = new EncodingService();
        user = getUser(user.getLoginName());

        String dbUserName = encodingService.getDBUserName(user);
        String dbPassword = encodingService.getDBPassword(user);
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(SQLStatements.CREATE_USER_SP);
            statement.setString(1, dbUserName);
            statement.setString(2, dbPassword);
            statement.executeQuery();
        } finally {
            close(statement);
        }
        assignLabeltoDBUser(dbUserName, user.getUserSubType(), user.getUserType());
    }

    private void assignLabeltoDBUser(String username, UserSubType subType, UserType userType) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareCall(SQLStatements.ASSIGN_LABEL);
            statement.setString(1, "DATA_ACCESS");
            statement.setString(2, username);
            statement.setString(5, "DEV");
            switch (subType) {
                case ASE:
                    statement.setString(3, "DEV::ASST");
                    statement.setString(4, "DEV::ASST");
                    statement.setString(6, "DEV::ASST");
                    statement.setString(7, "DEV::ASST");
                    break;
                case SE:
                    statement.setString(3, "DEV::DEF");
                    statement.setString(4, "DEV::DEF");
                    statement.setString(6, "DEV::DEF");
                    statement.setString(7, "DEV::DEF");
                    break;
                case SSE:
                    statement.setString(3, "DEV::SNR");
                    statement.setString(4, "DEV::SNR");
                    statement.setString(6, "DEV::SNR");
                    statement.setString(7, "DEV::SNR");
                    break;
                case NONE:
                    if (userType == UserType.PM) {
                        statement.setString(3, "PM::SNR");
                        statement.setString(4, "PM::SNR");
                        statement.setString(6, "PM::SNR");
                        statement.setString(7, "PM::SNR");
                    } else {
                        statement.setString(3, "HR::SNR");
                        statement.setString(4, "HR::SNR");
                        statement.setString(6, "HR::SNR");
                        statement.setString(7, "HR::SNR");
                    }
            }
            statement.executeQuery();
        } finally {
            close(statement);
        }
    }

    public void deleteDatabaseUser(User user) throws SQLException {
        EncodingService encodingService = new EncodingService();
        String dbUserName = encodingService.getDBUserName(user);
        String dbPassword = encodingService.getDBPassword(user);
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(SQLStatements.DELETE_USER_PASSWORD_SP);
            statement.setString(1, dbUserName);
            statement.setString(2, dbPassword);
            statement.executeQuery();
        } finally {
            close(statement);
        }
    }

    public void updateUser(User oldUser, User newUser) {
        PreparedStatement statement = null;
        try {
            setAutoCommit(false);
            updateUserEntity(newUser);
            if (newUser.getUserType().equals(UserType.HR_MGR) || newUser.getUserType().equals(UserType.HR_ASST)) {
                deleteAssignedProjects(newUser);
            }
            if(!oldUser.getUserType().equals(newUser.getUserType()) || !oldUser.getUserSubType().equals(newUser.getUserSubType())){
                deleteDatabaseUser(oldUser);
                createDatabaseUser(newUser);
            }
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }


    public void updateUserEntity(User user) throws SQLException {
        ConversionService conversionService = new ConversionService();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.UPDATE_USER_SQL);
            statement.setString(1, user.getLastName());
            statement.setString(2, user.getFirstName());
            statement.setString(3, user.getAddress());
            statement.setLong(4, user.getPhoneNumber());
            statement.setDate(5, new java.sql.Date(user.getDateOfBirth().getTime()));
            statement.setLong(6, user.getBasicSalary());

            int userTypeDB = conversionService.getUserType(user.getUserType());
            statement.setInt(7, userTypeDB);

            statement.setInt(8, user.getRemainingLeaves());


            int userSubTypeDB = conversionService.getUserSubTypeDB(user.getUserSubType());
            statement.setInt(9, userSubTypeDB);

             statement.setString(10, user.getLoginName());

            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    public void deleteAssignedProjects(User user) throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.DELETE_ASSIGNED_LIST);
            statement.setInt(1, user.getUserId());
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    public List<User> loadUsers() {
        PreparedStatement statement = null;
        ResultSet resultSet;
        User user;
        List<User> userList = new ArrayList<User>();
        try {
            statement = connection.prepareStatement(SQLStatements.GET_ALL_USER_DATA_SQL);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                user = DataReadUtils.getUserInformation(resultSet);
                userList.add(user);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return userList;
    }

    public boolean isActiveProjectsAvailable(int userId) {
        PreparedStatement statement = null;
        ResultSet resultSet;
        boolean isActiveProjectsAvailable = false;
        int projectStatusDB;
        ConversionService conversionService = new ConversionService();
        try {
            statement = connection.prepareStatement(SQLStatements.GET_ASSIGNED_ACTIVE_PROJECTS);
            projectStatusDB = conversionService.getProjectStatusDB(ProjectStatus.ACTIVE);
            statement.setInt(1, userId);
            statement.setInt(2, projectStatusDB);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isActiveProjectsAvailable = true;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return isActiveProjectsAvailable;
    }

    public void deleteUser(User user) {
        try {
            setAutoCommit(false);
            updateUser(user.getUserId(), UserStatus.DELETED);
            deleteDatabaseUser(user);
            commit();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            setAutoCommit(true);
        }
    }

    public void activateUser(User user) {
        try {
            setAutoCommit(false);
            updateUser(user.getUserId(), UserStatus.ACTIVE);
            clearFailedLoginAttempts(user);
            createDatabaseUser(user);
            commit();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            setAutoCommit(true);
        }
    }

    public void updateUser(int userId, UserStatus userStatus) throws SQLException {
        PreparedStatement statement = null;
        int userStatusDB;
        ConversionService conversionService = new ConversionService();
        try {
            statement = connection.prepareStatement(SQLStatements.UPDATE_USER_STAUS_SQL);
            userStatusDB = conversionService.getUserStatusDB(userStatus);
            statement.setInt(1, userStatusDB);
            statement.setInt(2, userId);
            statement.executeUpdate();

        } finally {
            close(statement);
        }
    }

    public boolean validateLoginName(String loginName) {
        PreparedStatement statement = null;
        ResultSet resultSet;
        boolean isValid = true;
        User user;
        try {
            statement = connection.prepareStatement(SQLStatements.VALIDATE_LOGIN_NAME);
            statement.setString(1, loginName);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                isValid = false;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
        return isValid;
    }

    public void updatePassword(String newPassword, User user) {
        PreparedStatement statement = null;
        try {
            setAutoCommit(false);
            updatePassword(newPassword, user.getUserId());
            updateDatabaseUser(user);
            commit();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            rollback();
        } finally {
            close(statement);
            setAutoCommit(true);
        }
    }

    public void updateDatabaseUser(User user) throws SQLException {
        EncodingService encodingService = new EncodingService();
        user = getUser(user.getLoginName());
        String dbUserName = encodingService.getDBUserName(user);
        String dbPassword = encodingService.getDBPassword(user);
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(SQLStatements.UPDATE_USER_PASSWORD_SP);
            statement.setString(1, dbUserName);
            statement.setString(2, dbPassword);
            statement.executeQuery();
        } finally {
            close(statement);
        }
    }

    public void updatePassword(String newPassword, int userId) throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.CHANGE_PASSWORD);
            statement.setString(1, newPassword);
            statement.setInt(2, userId);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

      public void blockUser(User user) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.BLOCK_USER);
            statement.setInt(1, user.getUserId());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public void clearFailedLoginAttempts(User user) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.CLEAR_FAILED_LOGIN_ATTEMPTS);
            statement.setInt(1, user.getUserId());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }

    public int getFailedLoginAttempts(User user) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int failedLoginAttempts = 0;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_FAILED_LOGIN_ATTEMPTS);
            statement.setInt(1, user.getUserId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                failedLoginAttempts = resultSet.getInt("FAILED_LOGIN_ATTEMPTS");
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return failedLoginAttempts;
    }

     public Date getLastFailedLoginAttemptTime(User user) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Timestamp failedLoginTimestamp = null;
         Date failedLoginTime = null;
        try {
            statement = connection.prepareStatement(SQLStatements.GET_LAST_FAILED_LOGIN_TIME);
            statement.setInt(1, user.getUserId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                failedLoginTimestamp = resultSet.getTimestamp("LAST_FAILED_LOGIN_TIME");
            }
            failedLoginTime = new Date(failedLoginTimestamp.getTime());
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement, resultSet);
        }
        return failedLoginTime;
    }

    public void increametFailedAttempt(User user) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.INCREMENT_FAILED_LOGIN);
            statement.setInt(1, user.getUserId());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }


    public void updateLastLoginFailedTime(User user){
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQLStatements.UPDATE_LAST_LOGIN_FAILED_TIME);
            statement.setInt(1, user.getUserId());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        } finally {
            close(statement);
        }
    }
}
